<?php
session_start();
include_once("config/config.inc.php");
include_once("reporting.php");
include_once("dailyreport.functions.php");
$room = $_GET["roomid"];

function getStartTime()
{
	$sql = "select datetime from `shift-transactions` where shift = 'start' order by datetime desc";
		
	$res = mysql_query($sql);
	while(list($time) = mysql_fetch_row($res))
	{
		return $time;
	}	
}

function getDailyReport()
{
	$end = date("Y-m-d H:i:s");
	$startshift = getStartTime();
	$ret .= getSalesInTransit();
	return $ret;
}

function getSalesTransaction($startshift,$end)
{
	$rooms = getCheckoutRoomDeposits($startshift,$end) + getExtensions($startshift,$end);
	$foods = getFoodTotalAmount($startshift,$end);
	$miscs = getMiscTotalAmount($startshift,$end);	
	$adjustments = getRoomAdjustmentTotal($startshift,$end);
	$grosssales = $rooms+$foods+$miscs+$adjustments;


	$drooms = getDiscountRoomTotalAmount($startshift,$end)
	$dfoods = "";
	$dmiscs = "";

	$taccountability = $grosssales-($drooms+$dfoods+$dmiscs)

	$ret .= "<table>";
	$ret .= "<tr>";
	$ret .= "<td>SALES TRANSACTION</td>";	
	$ret .= "<td>&nbsp;</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>Room Sales Order</td>";	
	$ret .= "<td>$rooms</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>FnB Sales Order</td>";	
	$ret .= "<td>$foods</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>Misc Sales Order</td>";	
	$ret .= "<td>$miscs</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>Room Sales Adjustment</td>";	
	$ret .= "<td>$adjustments</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>GROSS SALES</td>";	
	$ret .= "<td>$grosssales</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>&nbsp;</td>";	
	$ret .= "<td>&nbsp;</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>DISCOUNT</td>";	
	$ret .= "<td>&nbsp;</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>Room</td>";	
	$ret .= "<td>$drooms</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>FnB</td>";	
	$ret .= "<td>$dfoods</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>Misc</td>";	
	$ret .= "<td>$dmiscs</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>&nbsp;</td>";	
	$ret .= "<td>&nbsp;</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>TOTAL ACCOUNTABILITY</td>";	
	$ret .= "<td>$taccountability</td>";
	$ret .= "</tr>";
	$ret .= "</table>";




	return $ret;
}

function getCDailyReport($cashdeclared)
{
	$tamount = 0;
	//echo  $startshift = "2009-12-01 00:09:16";
	//echo $end = "2009-12-01 06:29:58";
	
	 $end = date("Y-m-d H:i:s");
	 $startshift = getStartTime();



	 $ret = getSalesInTransit();
	 return $ret;

	$foodtotal = getFoodTotalAmount($startshift,$end);
	$misctotal = getMiscTotalAmount($startshift,$end);	
	$deposittotal = getDepositsFromSalesReceipts($startshift,$end);	
	echo "<b>".getCheckinRoomDeposits($startshift,$end)."x</b>";
	echo "<b>".getExtensions($startshift,$end)."x</b>";
	$roomdeposit = getCheckinRoomDeposits($startshift,$end) + getExtensions($startshift,$end);
	$totalgross = $foodtotal+$misctotal+$deposittotal+$roomdeposit;

	
	$roomadjustment = getRoomAdjustmentTotal($startshift,$end); //?
	

	$roomgross = getRoomGross($startshift,$end);	
	$card = getPaidByCard($startshift,$end);
	$totalaccountability = $totalgross-($card+$roomgross);
	//$totalaccountability= $totalgross-($deposittotal+$refundtotal);
	//$fooddeposit = getCheckinFoodDeposits($startshift,$end);
	
	$refundtotal = getRefundsFromSalesReceipts($startshift,$end);	//? 
	
		
	$cashdeclaration = getCashDeclaration($cashdeclared);
	$fundssafekeep = getSafekeepAmountByTimeframe($startshift,$end);
	$totalcashdeclared = $cashdeclaration + $fundssafekeep;
	$overshortages = $totalcashdeclared - $totalaccountability;
	$ret.="SHOGUN HOTEL";
	$ret.="<br>";
	$ret.="Cashiers Daily Report";
	$ret.="<br>";
	$ret.=$now;
	$ret.="<br><br>";

	$ret .= "<table>";
	
	
	

	



	
	$ret .="<tr><td>ROOM ADJUSTMENT: </td><td>&nbsp;</td><td>&nbsp;</td><td>".$roomadjustment."</td>";
	$ret .="<tr><td>FOOD: </td><td>&nbsp;</td><td>&nbsp;</td><td>".$foodtotal."</td>";
	$ret .="<tr><td>MISCELLANEOUS: </td><td>&nbsp;</td><td>&nbsp;</td><td>".$misctotal."</td>";
	$ret .="<tr><td>DEPOSITS</td><td>&nbsp;</td><td>&nbsp;</td><td>". $deposittotal ."</td>";
	$ret .="<tr><td>ROOM DEPOSITS</td><td>&nbsp;</td><td>&nbsp;</td><td>". $roomdeposit ."</td>";		
	$ret .="<tr><td><b>GROSS SALES: </b></td><td>&nbsp;</td><td>&nbsp;</td><td><b>".$totalgross."</b></td>";
	$ret .="<tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>";
	$ret .=getAmountRoomTotal($startshift,$end);
	$ret .="<tr><td>ROOM GROSS: </td><td>&nbsp;</td><td>&nbsp;</td><td>".$roomgross."</td>";
	$ret .=getDiscountRoomTotal($startshift,$end);
	$ret .="<tr><td>CARD: </td><td>&nbsp;</td><td>&nbsp;</td><td>".$card."</td>";
	//$ret .="<tr><td>FOOD DEPOSITS</td><td>&nbsp;</td><td>&nbsp;</td><td>". $fooddeposit ."</td>";	
	
	$ret .="<tr><td>REFUNDS</td><td>&nbsp;</td><td>&nbsp;</td><td>". $refundtotal ."</td>";
	
	
	$ret .="<tr><td>TOTAL ACCOUNTABILITY: </td><td>&nbsp;</td><td>&nbsp;</td><td>".$totalaccountability."</td>";
	$ret .="<tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>";
	$ret .="<tr><td>CASH DECLARATION: </td><td>&nbsp;</td><td>&nbsp;</td><td>".$cashdeclaration."</td>";
	$ret .="<tr><td>FUNDS SAFEKEEP: </td><td>&nbsp;</td><td>&nbsp;</td><td>".$fundssafekeep."</td>";
	$ret .="<tr><td>TOTAL CASH DECLARED: </td><td>&nbsp;</td><td>&nbsp;</td><td>".$totalcashdeclared."</td>";
	$ret .="<tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>";	
	$ret .="<tr><td>OVER/SHORTAGES: </td><td>&nbsp;</td><td>&nbsp;</td><td>".$overshortages."</td>";
	$ret .= "</table>";
	return $ret;
}

function getPrintableCDailyReport($cashdeclared)
{
	$tamount = 0;
	$end = date("Y-m-d H:i:s");
	$startshift = getStartTime();	
	
	$foodtotal = getFoodTotalAmount($startshift,$end);
	$misctotal = getMiscTotalAmount($startshift,$end);	
	$deposittotal = getDepositsFromSalesReceipts($startshift,$end);	
	echo "<b>".getCheckinRoomDeposits($startshift,$end)."</b>";
	echo "<b>".getExtensions($startshift,$end)."</b>";
	$roomdeposit = getCheckinRoomDeposits($startshift,$end) + getExtensions($startshift,$end);
	$totalgross = $foodtotal+$misctotal+$deposittotal+$roomdeposit;	
	$roomadjustment = getRoomAdjustmentTotal($startshift,$end); //?
	$roomgross = getRoomGross($startshift,$end);	
	$card = getPaidByCard($startshift,$end);
	$totalaccountability = $totalgross-($card+$roomgross);	
	$refundtotal = getRefundsFromSalesReceipts($startshift,$end);	//?		
	$cashdeclaration = getCashDeclaration($cashdeclared);
	$fundssafekeep = getSafekeepAmountByTimeframe($startshift,$end);
	$totalcashdeclared = $cashdeclaration + $fundssafekeep;
	$overshortages = $totalcashdeclared - $totalaccountability;

	$ret.="

	SHOGUN HOTEL
	Cashiers Daily Report
	$end	
	";	
	//$ret.=getPrintableAmountRoomTotal($startshift,$end)."\n";
	$ret.="\nAdjustment: \t\t\t " .$roomadjustment."\n";
	$ret.="Food: \t\t\t\t " . $foodtotal."\n";
	$ret.="Miscellaneous: \t\t\t " . $misctotal."\n";
	$ret.="Deposits: \t\t\t " . $deposittotal."\n";
	$ret.="Room Deposits: \t\t\t " . $roomdeposit."\n";
	$ret.="Gross Sales: \t\t\t " . $totalgross."\n";
	$ret.="\n";
	$ret.=getPrintableAmountRoomTotal($startshift,$end);
	$ret.="\n";
	$ret.="Room Gross: \t\t\t ".$roomgross;	
	$ret.="\n";
	$ret.="\n";
	$ret.=getPrintableDiscountRoomTotal($startshift,$end);
	$ret.="\n";
	$ret.="\n";
	$ret.="Card: \t\t\t " . $card."\n";
	$ret.="Refund: \t\t\t " . $refundtotal."\n";
	$ret.="Total Accountability: \t\t " . $totalaccountability."\n";
	$ret.="\n";
	$ret.="Cash Declaration: \t\t ".$cashdeclaration."\n";
	$ret.="Funds Safekeep: \t\t " . $fundssafekeep."\n";
	$ret.="Total Cash Declared: \t\t " . $totalcashdeclared."\n";
	$ret.="\n";
	$ret.="Over Shortages: \t\t " . $overshortages."\n";
	
	
	return $ret;
}

function getRoomGross($startdt,$end)
{
	$sql = "select a.occupancy_id from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.actual_checkout >= '".$startdt."'
	and a.actual_checkout <= '".$end."'";	
	
	$res = mysql_query($sql) or die(mysql_error().$sql);
	
	while(list($occupancy_id)=mysql_fetch_row($res))
	{		
		$id .= "'".$occupancy_id."',";
	}
	$id  = substr_replace($id ,"",-1);
	if($id!="")
	{	
		$_sql = "select unit_cost,qty from room_sales 
		where (item_id ='16' 
		or item_id ='15' 
		or item_id = '18') 
		and occupancy_id in ($id) 
		and status in ('Paid')";
		$_res = mysql_query($_sql) or die(mysql_error().$_sql);
		while(list($unit_cost,$qty)=mysql_fetch_row($_res))
		{
			$num = $unit_cost * $qty;
			if($num > 0)
			{
				$total = $total + ($unit_cost * $qty);
			}	
		}
	}
	
	return $total;
}

function getExtensions($startdt,$end)
{
	$sql = "select unit_cost,qty from room_sales
		where status in ('Paid')
		and update_date >= '$startdt'
		and update_date <= '$end'
		and item_id = '16'";
	$res = mysql_query($sql);
	while(list($unit_cost,$qty)=mysql_fetch_row($res))
	{
		$total = $total + ($unit_cost*$qty);
	}

	return $total;	
}

function getCheckinRoomDeposits($startdt,$end)
{
	
	$sql = "select unit_cost,qty from room_sales where status in ('Paid')
	and update_date >= '$startdt'
	and update_date <= '$end'
	and item_id = '15'";
	/*$sql = "select sum(c.amount)
	from occupancy a,rooms b, room_type_rates c
	where a.room_id = b.room_id
	and a.rate_id = c.rate_id
	and b.room_type_id = c.room_type_id
	and a.actual_checkin >= '".$startdt."'
	and a.actual_checkin <= '".$end."'
	and a.actual_checkout = '0000-00-00 00:00:00'";*/
	$res = mysql_query($sql);
	while(list($unit_cost,$qty)=mysql_fetch_row($res))
	{
		$total = $total + ($unit_cost*$qty);
	}
	return $total;
}


/*function getCheckinFoodDeposits($startshift,$end)
{
	$sql = "select unit_cost from fnb_sales,occupancy 
	where fnb_sales.sales_date >= '".$startdt."' 
	and fnb_sales.sales_date <= '".$end."'
	and occupancy.actual_checkout <> '0000-00-00 00:00:00'
	and occupancy.occupancy_id = fnb_sales.occupancy_id
	and fnb_sales.status in ('Paid')";
	
	list($amount)=mysql_fetch_row(mysql_query($sql));
	return $amount;
}*/

function getPaidByCard($startshift,$end)
{
	$sql = "select sum(amount) from salesreceipts  
	where receipt_date >= '$startshift' 
	and receipt_date <= '$end' 
	and tendertype='Card'";
	$res = mysql_query($sql) or die(mysql_error($sql));
	list($sum)=mysql_fetch_row($res);
	return $sum;
}
function getDepositsFromSalesReceipts($startshift,$end) {
	$sql = " select sum(amount) from salesreceipts where receipt_date >= '$startshift' and receipt_date <= '$end' and tendertype='Deposit' ";
	$res = mysql_query($sql) or die(mysql_error($sql));
	list($sum)=mysql_fetch_row($res);
	if(!$sum)
	{
		$sum = 0;
	}
	return $sum;
}

function getRefundsFromSalesReceipts($startshift,$end) {
	//$sql = " select sum(amount) from salesreceipts where receipt_date >= '$startshift' and receipt_date <= '$end' 
	//     and tendertype='Refund' ";
	$sql = "select sum(amount) from salesreceipts 
	where receipt_date >= '$startshift' 
	and receipt_date <= '$end'
	and tendertype='Cash'
	and amount like '-%'";
	$res = mysql_query($sql);
	list($sum)=mysql_fetch_row($res);
	return $sum;
}

function getTotalCashDeclared($cashdeclared,$startshift,$end)
{
	$total = $cashdeclared + getSafekeepAmountByTimeframe($startshift,$end);
	return $total;
}





function getPrintableAmountRoomTotal($startshift,$end)
{
	$sql = "SELECT `room_type_id`, `room_type_name` FROM `room_types` ";
	$res = mysql_query($sql);
	$ret .= "\n";
	$ret .= "ROOM  ";
	while(list($room_type_id,$room_type_name) = mysql_fetch_row($res))
	{
		if($room_type_name == "Regent" || $room_type_name == "Queen")
		{
			$t = "\t\t\t";
		}
		else if($room_type_name == "Executive Suite")
		{
			$t = "\t";
		}
		else
		{
			$t = "\t\t";
		}
		$t .= getNumRoomByTypeID($room_type_id,$startshift,$end)."\t";

		
		$ret .= "\n";		
		$ret .= "$room_type_name $t".getAmountByRoomTypeID($room_type_id,$startshift,$end);
		$i++;
	}
	return $ret;
}

function getAmountRoomTotal($startshift,$end)
{
	$sql = "SELECT `room_type_id`, `room_type_name` FROM `room_types` ";
	$res = mysql_query($sql);
	
	while(list($room_type_id,$room_type_name) = mysql_fetch_row($res))
	{
		$ret .= "<tr>";
		if($i == 0)
		{
			$ret .= "<td>ROOM</td>";
		}
		else
		{
			$ret .= "<td>&nbsp;</td>";
		}
		$ret .= "<td>".$room_type_name."</td><td>".getNumRoomByTypeID($room_type_id,$startshift,$end)."</td><td>".getAmountByRoomTypeID($room_type_id,$startshift,$end)."</td></tr>";
		$i++;
	}
	return $ret;
}
function getNumRoomByTypeID($room_type_id,$startshift,$end)
{
	echo $sql = "select a.occupancy_id from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.actual_checkout >= '".$startshift."'
	and a.actual_checkout <= '".$end."'
	and b.room_type_id = '$room_type_id'";
	$res = mysql_query($sql);
	$num = mysql_num_rows($res);
	return $num;
}
function getAmountByRoomTypeID($roomtypeid,$startdt,$end)
{
	
	 /*$sql = "select a.occupancy_id 
	from occupancy a,rooms b, room_type_rates c
	where a.room_id = b.room_id
	and a.rate_id = c.rate_id
	and b.room_type_id = c.room_type_id
	and a.actual_checkout >= '".$startdt."' 
	and a.actual_checkout <= '".$end."'
	and b.room_type_id = '".$roomtypeid."'";*/

	$sql = "select a.occupancy_id from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.actual_checkout >= '".$startdt."'
	and a.actual_checkout <= '".$end."'
	and b.room_type_id = '$roomtypeid'";
	
	$res = mysql_query($sql) or die(mysql_error().$sql);
	
	while(list($occupancy_id)=mysql_fetch_row($res))
	{		
		$id .= "'".$occupancy_id."',";
	}
	$id  = substr_replace($id ,"",-1);
	if($id!="")
	{	
		$_sql = "select unit_cost,qty from room_sales 
		where (item_id ='16' 
		or item_id ='15' 
		or item_id = '18') 
		and occupancy_id in ($id) 
		and status in ('Paid')";
		$_res = mysql_query($_sql) or die(mysql_error().$_sql);
		while(list($unit_cost,$qty)=mysql_fetch_row($_res))
		{
			$num = $unit_cost * $qty;
			if($num > 0)
			{
				$total = $total + ($unit_cost * $qty);
			}	
		}
	}
	
	return $total;
}
function getTotalGross($startdt,$end)
{
	
	/*$sql = "select sum(c.amount)
	from occupancy a,rooms b, room_type_rates c
	where a.room_id = b.room_id
	and a.rate_id = c.rate_id
	and b.room_type_id = c.room_type_id
	and a.actual_checkout >= '".$startdt."' 
	and a.actual_checkout <= '".$end."'";*/
	$sql = "select b.unit_cost, b.qty from occupancy a,room_sales b 
	where a.occupancy_id = b.occupancy_id
	and (b.item_id = '15' 
	or b.item_id = '16'
	or b.item_id = '18')
	and a.actual_checkout >= '$startdt'
	and a.actual_checkout <= '$end'
	and b.status in ('Paid')";
	$res = mysql_query($sql);

	while(list($unit_cost,$qty)=mysql_fetch_row($res))
	{
		$num = $unit_cost * $qty;
		if($num > 0)
		{
			$total = $total + ($unit_cost * $qty);
		}
		
	}

	$sql = "select b.unit_cost, b.qty from occupancy a,room_sales b 
	where a.occupancy_id = b.occupancy_id	
	and b.item_id = '18'	
	and a.actual_checkout >= '$startdt'
	and a.actual_checkout <= '$end'
	and b.status in ('Paid')";
	$res = mysql_query($sql);
	while(list($unit_cost,$qty)=mysql_fetch_row($res))
	{
		$num = $unit_cost * $qty;
		if($num > 0)
		{
			$total = $total + $num;
		}
	}

	//$total+=getMiscTotalAmount($startdt,$end);
	//$total+=getFoodTotalAmount($startdt,$end);
	return $total;
}

function getStartShiftTime($datetime,$end)
{
	list($date,$time)=explode(" ",$datetime);
	list($hour,$min,$sec)=explode(":",$time);
	
	$sql = "select shift_start from shifts where shift_id = '".getShiftID($end)."'";
	$res = mysql_query($sql);
	while(list($shift_start)=mysql_fetch_row($res))
	{
		$dt = $date." ".$shift_start.":00:00";
	}	
	return $dt;

}
function getShiftID($datetime)
{
	list($date,$time)=explode(" ",$datetime);
	list($hour,$min,$sec)=explode(":",$time);
	$sql = "select * from shifts where shift_end > '$hour' and shift_start <='$hour'";	
	$res = mysql_query($sql);
	list($shift_id) = mysql_fetch_row($res);
	return $shift_id;
}

function getFoodTotalAmount($startdt,$end)
{
	
	$total = 0;
	
	$sql = "select unit_cost,qty from fnb_sales
	where update_date >= '".$startdt."' 
	and update_date <= '".$end."'
	and status in ('Paid') ";	
	$res=mysql_query($sql);
	while(list($unit_cost,$qty)=mysql_fetch_row($res))
	{
		$total = $total + ($unit_cost*$qty);
	}
	return $total;
}

function getMiscTotalAmount($startdt,$end)
{
	
	$total = 0;
	 $sql = "select unit_cost,qty from room_sales  a, occupancy b, sales_and_services c,sas_category d
		where a.item_id=c.sas_id
		and b.occupancy_id = a.occupancy_id
		and c.sas_cat_id = d.sas_cat_id
		and d.sas_cat_id < 3
		and a.update_date >= '".$startdt."'
		and a.update_date <= '".$end."'
		and a.status in ('Paid')";
		
	$res=mysql_query($sql);
	while(list($unit_cost,$qty)=mysql_fetch_row($res))
	{
		$total = $total + ($unit_cost*$qty);
	}
	return $total;
}

function getRoomAdjustmentTotal($startdt,$end)
{
	
	$total = 0;
	$sql = "select unit_cost,qty from room_sales  a, occupancy b, sales_and_services c,sas_category d
		where a.item_id=c.sas_id
		and b.occupancy_id = a.occupancy_id
		and c.sas_cat_id = d.sas_cat_id
		and c.sas_id = '18'
		and b.actual_checkout >= '".$startdt."'
		and b.actual_checkout <= '".$end."'
		and a.status in ('Paid')";
	$res=mysql_query($sql);
	while(list($unit_cost,$qty)=mysql_fetch_row($res))
	{
		$total = $total + ($qty * $unit_cost);		
	}
	return $total;
}

function getPrintableDiscountRoomTotal($startshift,$end)
{
	$sql = "SELECT `room_type_id`, `room_type_name` FROM `room_types` ";
	$res = mysql_query($sql);
	$ret .= "DISCOUNT ";
	while(list($room_type_id,$room_type_name) = mysql_fetch_row($res))
	{
		if($room_type_name == "Regent" || $room_type_name == "Food")
		{
			$t = "\t\t\t";
		}		
		else if($room_type_name == "Executive Suite")
		{
			$t = "\t";
		}
		else
		{
			$t = "\t\t";
		}
		$ret .= "\n";		
		$ret .= "$room_type_name $t  ".getDiscountByRoomTypeID($room_type_id,$startshift,$end);		
	}
	return $ret;
}
function getDiscountRoomTotal($startdt,$end)
{
	$sql = "SELECT `room_type_id`, `room_type_name` FROM `room_types` ";
	$res = mysql_query($sql);

	while(list($room_type_id,$room_type_name) = mysql_fetch_row($res))
	{
		$ret .= "<tr>";
		if($i == 0)
		{
			$ret .= "<td>DISCOUNT</td>";
		}
		else
		{
			$ret .= "<td>&nbsp;</td>";
		}
		$ret .= "<td>".$room_type_name."</td><td>&nbsp;</td><td>".getDiscountByRoomTypeID($room_type_id,$startdt,$end)."</td></tr>";
		$i++;
	}	
	return $ret;
}
function getDiscountByRoomTypeID($room_type_id,$startdt,$end)
{
	/*$sql = "select c.discount_given 
	from occupancy a,rooms b, discount_log c
	where a.room_id = b.room_id	
	and a.occupancy_id = c.occupancy_id	
	and a.actual_checkout >= '".$startdt."' 
	and a.actual_checkout <= '".$end."'
	and b.room_type_id = '".$room_type_id."'";*/

	$sql = "select a.occupancy_id from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.actual_checkout >= '".$startdt."'
	and a.actual_checkout <= '".$end."'
	and b.room_type_id = '$room_type_id'";

	$res = mysql_query($sql) or die(mysql_error().$sql);
	
	while(list($occupancy_id)=mysql_fetch_row($res))
	{
		$id .= "'".$occupancy_id."',";
	}
	$id  = substr_replace($id ,"",-1);
	if($id!="")
	{	
		$_sql = "select unit_cost,qty from room_sales 
		where item_id ='17'  
		and occupancy_id in ($id) 
		and status in ('Paid')";
		$_res = mysql_query($_sql) or die(mysql_error().$_sql);
		while(list($unit_cost,$qty)=mysql_fetch_row($_res))
		{
			$num = $unit_cost * $qty;
			if($num > 0)
			{
				$total = $total + ($unit_cost * $qty);
			}	
		}
	}
	
	return $total;



	$res = mysql_query($sql);
	$amount = 0;
	while(list($discount_given)=mysql_fetch_row($res))
	{
		$amount += $discount_given;		
	}	
	return -($amount);
}

function getDiscountRoomTotalAmount($startdt,$end)
{
	$sql = "select c.discount_given 
	from occupancy a,rooms b, discount_log c
	where a.room_id = b.room_id	
	and a.occupancy_id = c.occupancy_id	
	and a.actual_checkout >= '".$startdt."' 
	and a.actual_checkout <= '".$end."'";	
	$res = mysql_query($sql);
	$amount = 0;
	while(list($discount_given)=mysql_fetch_row($res))
	{
		$amount += $discount_given;		
	}	
	return -($amount);
}

function getTotalAccountability($startdt,$end)
{
	
	/*$sql = "select c.discount_given 
	from occupancy a,rooms b, discount_log c
	where a.room_id = b.room_id	
	and a.occupancy_id = c.occupancy_id	
	and a.actual_checkout >= '".$startdt."' 
	and a.actual_checkout <= '".$end."'";

	$res = mysql_query($sql);
	$amount = 0;
	while(list($discount_given)=mysql_fetch_row($res))
	{
		$amount += $discount_given;		
	}*/	
	//$total = getTotalGross($startdt,$end) - (getRoomAdjustmentTotal($startdt,$end)  + //getDiscountRoomTotalAmount($startdt,$end));
	
	return $total;
}

function getCashDeclaration($cashdeclared)
{
	$ret = $cashdeclared;
	return $ret;
}

function getSalesByTimeframe($start,$end)
{
	 
	$ret = 0;
	$sql = "select a.amount from salesreceipts a, occupancy b where receipt_date >= '$start' and receipt_date <= '$end' and tendertype = 'Cash' and b.occupancy_id <> '0000-00-00 00:00:00' and a.occupancy_id = b.occupancy_id";
	$res = mysql_query($sql);
	while(list($amount)=mysql_fetch_row($res))
	{
		$ret += $amount;
	}
	return $ret;
}
function getSafekeepAmountByTimeframe($start, $end)
{


	$ret = 0;
	
	$sql = "select current_amount from safekeep where safekeep_date >= '$start' and safekeep_date <= '$end' 
	order by safekeep_date desc, safekeep_id desc";


	$res = mysql_query($sql);
	list($ret)=mysql_fetch_row($res);
	return $ret;
}

function getSalesInTransit()
{
	$sql = "select * from occupancy where actual_checkout = '0000-00-00 00:00:00'";
	$res = mysql_query($sql);
	$retval .= "<table cellpadding=2 cellspacing=2 border=1>";
	while($row = mysql_fetch_array($res))
	{
		$_sql = "select door_name from rooms where room_id ='".$row["room_id"]."'";
		$_res = mysql_query($_sql);
		list($door_name)=mysql_fetch_row($_res);

		$_sql = "select rate_name from rates where rate_id ='".$row["rate_id"]."'";
		$_res = mysql_query($_sql);
		list($rate_name)=mysql_fetch_row($_res);

		$_sql = "select unit_cost,qty from room_sales 
		where (item_id ='16' 
		or item_id ='15' 
		or item_id = '18') 
		and occupancy_id = '".$row["occupancy_id"]."'
		and status in ('Paid')";
		$_res = mysql_query($_sql) or die(mysql_error().$_sql);
		$deposit =0;
		while(list($unit_cost,$qty)=mysql_fetch_row($_res))
		{
			$num = $unit_cost * $qty;
			if($num > 0)
			{
				$deposit = $deposit + ($unit_cost * $qty);
			}	
		}

		$_sql = "select unit_cost,qty from fnb_sales
		where status in ('Paid') 
		and occupancy_id = '".$row["occupancy_id"]."'";
		$_res=mysql_query($_sql);
		$order=0;
		while(list($unit_cost,$qty)=mysql_fetch_row($_res))
		{
			$order = $order + ($unit_cost*$qty);
		}


		$_sql = "select unit_cost,qty from room_sales  a, occupancy b, sales_and_services c,sas_category d
		where a.item_id=c.sas_id
		and b.occupancy_id = a.occupancy_id
		and c.sas_cat_id = d.sas_cat_id
		and d.sas_cat_id < 3
		and a.occupancy_id = '".$row["occupancy_id"]."'
		and a.status in ('Paid')";
		
		$_res=mysql_query($_sql);
		while(list($unit_cost,$qty)=mysql_fetch_row($_res))
		{
			$order = $order + ($unit_cost*$qty);
		}

		
		$retval .= "<tr>";
		$retval .= "<td>".$row["occupancy_id"]." ".$door_name."</td>";
		$retval .= "<td>".$row["actual_checkin"]."</td>";
		$retval .= "<td>".$rate_name."</td>";
		$retval .= "<td>".$deposit."</td>";
		$retval .= "<td>".$order."</td>";
		$retval .= "</tr>";
	}
	$retval .= "</table>";

	return $retval;
}
?>




